﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_BrowseEnterprises')
	BEGIN
		DROP  Procedure  [esf_sso].P_BrowseEnterprises
	END
GO

-- [esf_sso].P_BrowseEnterprises @pBasicReferenceTypeID = 1, @pPageIndex = 0, @pPageSize = 4
-- [esf_sso].P_BrowseEnterprises @pBasicReferenceTypeID = 1, @pPageIndex = 1, @pPageSize = 4
-- [esf_sso].P_BrowseEnterprises @pBasicReferenceTypeID = 1, @pPageIndex = 2, @pPageSize = 4

CREATE Procedure [esf_sso].P_BrowseEnterprises
	@pEnterpriseID			BIGINT = NULL
	,@pIdentificationTypeID	INT = NULL
	,@pIdentificationNumber	VARCHAR (60) = NULL
	,@pName					VARCHAR (60) = NULL
	,@pIsEnabled			BIT = NULL
	,@pPageIndex			BIGINT = 0
	,@pPageSize				BIGINT = 30
AS

	SET NOCOUNT ON

	SELECT * FROM
		(SELECT
			ROW_NUMBER () OVER (ORDER BY VEnt.Ent_Name) AS Ri
			,COUNT(*) OVER () AS RCount
			,VEnt.Ent_ID
			,VEnt.Ent_Code
			,VEnt.Ent_Name
			,VEnt.Ent_IdentificationNumber
			,VEnt.Ent_IdentificationNumberDigit
			,VEnt.Ent_IdType_Code
			,VEnt.Ent_IsEnabled
		FROM 
			[esf_sso].VEnterprise VEnt
		WHERE 
			(@pEnterpriseID IS NULL OR VEnt.Ent_ID = @pEnterpriseID)
			AND (@pIdentificationTypeID IS NULL OR VEnt.Ent_IdType_ID = @pIdentificationTypeID)
			AND (@pIdentificationNumber IS NULL OR VEnt.Ent_IdentificationNumber LIKE @pIdentificationNumber)
			AND (@pName IS NULL OR VEnt.Ent_Name LIKE @pName)
			AND (@pIsEnabled IS NULL OR VEnt.Ent_IsEnabled = @pIsEnabled)
		) T
	WHERE
		T.Ri BETWEEN (@pPageIndex * @pPageSize + 1) AND (@pPageIndex + 1) * @pPageSize
	ORDER BY 
		T.Ri

GO 